package es.xpt.ysf.commons.location.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import es.xpt.ysf.commons.location.model.Country;
import es.xpt.ysf.dao.Connection;
import es.xpt.ysf.dao.DAOErrors;
import es.xpt.ysf.dao.DAOException;
import es.xpt.ysf.dao.DAOUtils;
import es.xpt.ysf.dao.DbDAO;
import es.xpt.ysf.dao.PreparedStatement;
import es.xpt.ysf.jaas.YSFPrincipal;
import es.xpt.ysf.model.TagMap;

/**
 * Mantenimento de paises, provincias y localidades
 * @author: joseignacio.garces@xplayat.es
 * @version YSF 1.0
 * @since 
 */

/**
 * Country Data Access Object (DAO). This class contains all database handling
 * that is needed to permanently store and retrieve Country object instances.
 */

public class CountryDAO extends DbDAO<Country> {

	public CountryDAO(Connection c, YSFPrincipal principal, DAOErrors errors) {
		super(c, principal, errors);
	}


	public List<Country> findAll(TagMap tags) throws DAOException {
		String selectStatement = "SELECT c.id CTRY_id,c.isoNum CTRY_isoNum,c.iso2 CTRY_iso2,c.iso3 CTRY_iso3, i.description CTRY_description, c.tags CTRY_tags, '"
				+ principal.getLocale()
				+ "' CTRY_locale"
				+ " FROM ysf_cl_country c left outer join ysf_cl_country_i18n i"
				+ " on c.id=i.id and i.locale='" + principal.getLocale() + "'";
				if ( tags!=null )
					selectStatement += " WHERE " + DAOUtils.getTagQuery("c.tags", tags);
				selectStatement += " ORDER BY description";
		return listQuery(selectStatement);
	}

	/**
	 * @param id
	 * @return
	 * @throws DAOException
	 */
	public List<Country> getCountry(String id) throws DAOException {
		String selectStatement = "SELECT c.id CTRY_id,c.isoNum CTRY_isoNum,c.iso2 CTRY_iso2,c.iso3 CTRY_iso3, i.description CTRY_description, c.tags CTRY_tags, locale CTRY_locale"
			+ " FROM ysf_cl_country c left outer join ysf_cl_country_i18n i"
			+ " on c.id=i.id where c.id = '" + id +"'"
			+ " ORDER BY CTRY_locale";
	
		return listQuery(selectStatement);
	}
	
	/**
	 * insert method will create new row in database according to supplied
	 * valueObject contents. Make sure that values for all NOT NULL columns are
	 * correctly specified. Also, if this table does not use automatic
	 * surrogate-keys the primary-key must be specified.
	 * 
	 * @param valueObject
	 *            This parameter contains the class instance to be created. If
	 *            automatic surrogate-keys are not used the Primary-key field
	 *            must be set for this to work properly.
	 * @exception DAOException
	 */
	public synchronized void insert(Country valueObject) throws DAOException {

		
		String statementi18n = "INSERT INTO ysf_cl_country_i18n ( id, locale, description )"
				+ " VALUES (?, ?, ?)";
		PreparedStatement stmt = null;
		try {

			
		
			stmt = con.prepareStatement(statementi18n);
			stmt.setString(1, valueObject.getCode());
			stmt.setString(2, valueObject.getLocale());
			stmt.setString(3, valueObject.getDescription());

			int rowcount = stmt.executeUpdate();
			if (rowcount != 1) {
				throw new DAOException("commons.location.countryInsertError");
			}
		} catch (SQLException se) {
			throw new DAOException("commons.location.countryInsertError", se);
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

	public void update(Country valueObject) throws DAOException {

		PreparedStatement stmt = null;
		try {
			String updateStatement = "UPDATE ysf_cl_country SET tags = ? WHERE id = ?";
			stmt = con.prepareStatement(updateStatement);
			if (valueObject.getTags() != null)
				stmt.setString(1, valueObject.getTags().getTagString());
			else 
				stmt.setString(1, "");
			stmt.setString(2, valueObject.getCode());			
			
			int rowcount = stmt.executeUpdate();
			if (rowcount == 0) {
				throw new DAOException("commons.location.countryModifyError");
			}
			if (rowcount > 1) {
				throw new DAOException("commons.location.countryModifyError");
			}

			String updateStatementi18n = "UPDATE ysf_cl_country_i18n SET description  = ? WHERE id = ? and locale=?";
			stmt = con.prepareStatement(updateStatementi18n);
			stmt.setString(1, valueObject.getDescription());
			stmt.setString(2, valueObject.getCode());
			stmt.setString(3, valueObject.getLocale());
			rowcount = stmt.executeUpdate();
			if (rowcount == 0) {
				throw new DAOException("commons.location.countryModifyError");
			}
			if (rowcount > 1) {
				throw new DAOException("commons.location.countryModifyError");
			}
		} catch (SQLException se) {
			throw new DAOException("commons.location.countryModifyError", se);
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

	/**
	 * The same method described above. In this case recieves the primary key in
	 * separated arguments.
	 */
	public void delete(String id, String locale) throws DAOException {
		String deleteStatement = "DELETE FROM ysf_cl_country_i18n WHERE id = ? and locale=?";

		PreparedStatement stmt = null;

		try {
			stmt = con.prepareStatement(deleteStatement);
			stmt.setString(1, id);
			stmt.setString(2, locale);

			int rowcount = stmt.executeUpdate();
			if (rowcount == 0) {
				throw new DAOException("commons.location.countryDeleteError");
			}
			if (rowcount > 1) {
				throw new DAOException("commons.location.countryDeleteError");
			}
		} catch (SQLException se) {
			throw new DAOException("commons.location.countryDeleteError", se);
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}


	
	public List<Country> getCountryByZone(String zone) throws DAOException {
		String selectStatement = "SELECT c.id CTRY_id,c.isoNum CTRY_isoNum,c.iso2 CTRY_iso2,c.iso3 CTRY_iso3, i.description CTRY_description, c.tags CTRY_tags, locale CTRY_locale"
				+ " FROM ysf_cl_country_i18n i"
				+ " INNER JOIN ysf_cl_country c"
				+ " ON c.id = i.id AND i.locale='"+principal.getLocale().toString()+"'"
				+ " INNER JOIN tio_zone_location TZNL"
				+ " ON i.id = TZNL.country AND TZNL.province IS NULL AND TZNL.city IS NULL AND TZNL.zone = '"+zone+"'";
		return listQuery(selectStatement);
	}


	@Override
	protected Country populateBean(ResultSet rs) throws SQLException {
		Country dto = populateCountryBean(rs, "CTRY");
		dto.setTags(new TagMap(rs.getString("CTRY_tags")));
		return dto;
	}

	public static Country populateCountryBean(ResultSet rs, String abbre) throws SQLException {
		Country dto = new Country();
		dto.setCode(rs.getString(abbre+"_id"));
		dto.setLocale(rs.getString(abbre+"_locale"));
		dto.setIsoNum(rs.getString(abbre+"_isoNum"));
		dto.setIso3(rs.getString(abbre+"_iso3"));
		dto.setIso2(rs.getString(abbre+"_iso2"));
		dto.setDescription(rs.getString(abbre+"_description"));
		return dto;
	}

	public List<Country> suggestCountries(String fragment) throws DAOException {
		String selectStatement = "SELECT i.id CTRY_id,c.isoNum CTRY_isoNum,c.iso2 CTRY_iso2,c.iso3 CTRY_iso3, i.description CTRY_description, c.tags CTRY_tags, i.locale CTRY_locale"
			+ " FROM ysf_cl_country_i18n i, ysf_cl_country c"
			+ " WHERE i.description LIKE '%" + fragment + "%' AND c.id=i.id";
		return listQuery(selectStatement);
	}
}